﻿Public Class CapNhatThongTinSP_DAO

    Public Shared Function SelectAll() As DataTable
        Dim dt As DataTable
        Dim strSQL As String = "select * from SanPham"
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

    Public Shared Function SelectBySanPham(_sp As CapNhatThongTinSP_DTO) As DataTable
        Dim dt As DataTable
        Dim strSQL As String = "select * from SanPham where TenSanPham like '%" & _sp.TenSP & "%'"
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

    Public Shared Function SelectByTenSanPham(_tenSP As String) As DataTable
        Dim dt As DataTable
        Dim strSQL As String = "Select * from SanPham where TenSanPham like'%" & _tenSP & "%'"
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

    Public Shared Sub Insert(_sp As CapNhatThongTinSP_DTO)
        Dim strSQL As String = "Insert into SanPham(MaSanPham,TenSanPham,DonViTinh,Gia) values('" & _sp.MaSP & "','" & _sp.TenSP & "','" & _sp.DonViTinh & "'," & _sp.Gia & " )"
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    Public Shared Sub Delete(_sp As CapNhatThongTinSP_DTO)
        Dim strSQL As String = "Delete from SanPham where MaSanPham ='" & _sp.MaSP & "'"
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    Public Shared Sub Update(_sp As CapNhatThongTinSP_DTO)
        Dim strSQL As String = "Update SanPham set MaSanPham ='" & _sp.MaSP & "' ,TenSanPham='" & _sp.TenSP & "' ,DonViTinh='" & _sp.DonViTinh & "',Gia=" & _sp.Gia & " where MaSanPham = '" & _sp.MaSP & "'"
        SQLProvider.ExecNoneQuery(strSQL)
    End Sub

    Public Shared Function TotalValue(iSoPhieu As String) As String

        Dim iTotalValue As String = 0
        Dim strSQL As String

        strSQL = "select SUM(SoLuong * DonGia) from ChiTietPhieuNhap where SoPhieu = '" & iSoPhieu & "'"
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        iTotalValue = dt.Rows(0).Item(0).ToString()
        Return iTotalValue
    End Function

    'tinh tong phieu xuat
    Public Shared Function TotalValuePX(iSoPhieu As String) As String

        Dim iTotalValue As String = 0
        Dim strSQL As String

        strSQL = "select SUM(SoLuong * DonGia) from ChiTietPhieuXuat where SoPhieu = '" & iSoPhieu & "'"
        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        iTotalValue = dt.Rows(0).Item(0).ToString()
        Return iTotalValue
    End Function

    Public Shared Function SelectByID(iMa As String) As CapNhatPhieuNhapKho_DTO
        Dim pn As New CapNhatPhieuNhapKho_DTO

        Dim strSQL As String
        strSQL = "select * from PhieuNhap where SoPhieu = '" & iMa & "'"

        Dim dt As DataTable

        dt = SQLProvider.ExecQuery(strSQL)
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If
        pn.MaKho = dt.Rows(0).Item(0).ToString()
        pn.MaNhaCungCap = (dt.Rows(0).Item(1).ToString())
        pn.MaSanPham = (dt.Rows(0).Item(2).ToString())

        Return pn
    End Function

    'lay so phieu xuat
    Public Shared Function SelectByIDPX(iMa As String) As CapNhatPhieuXuatKho_DTO
        Dim px As New CapNhatPhieuXuatKho_DTO

        Dim strSQL As String
        strSQL = "select * from PhieuXuat where SoPhieu = '" & iMa & "'"

        Dim dt As DataTable

        dt = SQLProvider.ExecQuery(strSQL)
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If
        px.MaKho = dt.Rows(0).Item(0).ToString()
        px.MaCongTy = (dt.Rows(0).Item(1).ToString())
        px.MaSanPham = (dt.Rows(0).Item(2).ToString())

        Return px
    End Function

    Public Shared Function SelectAllPN(iMaphieu As String) As DataTable
        Dim strSQL As String
        strSQL = "select * from ChiTietPhieuNhap where SoPhieu = '" & iMaphieu & "'"

        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function

    'phieu xuat
    Public Shared Function SelectAllPX(iMaphieu As String) As DataTable
        Dim strSQL As String
        strSQL = "select * from ChiTietPhieuXuat where SoPhieu = '" & iMaphieu & "'"

        Dim dt As DataTable
        dt = SQLProvider.ExecQuery(strSQL)
        Return dt
    End Function
End Class




